package appapi.dao;

import appapi.Request.record.FaceRecordReq;
import appapi.Vo.brider.FaceRecordVo;
import appapi.dto.bridge.FaceRecordDto;
import appapi.entity.Zhongjiao.FaceRecordPageReq;
import appapi.entity.bridge.CameraEntity;
import appapi.entity.bridge.FaceRecordEntity;
import appapi.entity.bridge.vo.HourlyStatsVO;
import appapi.entity.bridge.vo.InOutCountVO;
import appapi.entity.bridge.vo.PersonVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author dhq
 * @version 1.0
 * @date 2023/9/11  12:26
 */
public interface FaceRecordMapper {
    int deleteByPrimaryKey(Long id);

    int insert(FaceRecordEntity record);

    int insertSelective(FaceRecordEntity record);

    FaceRecordEntity selectByPrimaryKey(Long id);

    int updateByPrimaryKeySelective(FaceRecordEntity record);

    int updateByPrimaryKey(FaceRecordEntity record);

    /**
     * 分页获取人脸识别记录
     *
     * @param faceRecordPageReq
     * @return
     */
    List<FaceRecordVo> getFaceRecordListByPage(@Param("faceRecordPageReq") FaceRecordPageReq faceRecordPageReq);

    List<FaceRecordVo> selectFaceRecordList(@Param("req") FaceRecordReq req);

    FaceRecordVo getDetail(Long id);

    @Select("select * from face_record")
    List<FaceRecordEntity> lists();

    @Select("SELECT f.* FROM face_record f \n" +
            "JOIN access_control a ON f.device_id = a.acce_id \n" +
            "JOIN project p ON a.proj_id = p.proj_id \n" +
            "JOIN organization o ON p.orga_id = o.orga_id \n" +
            "WHERE o.orga_id = #{orgaId} " +
            "ORDER BY f.create_time ASC LIMIT 5 ")
    List<FaceRecordEntity> getPersonOutCount(@Param("orgaId") Integer orgaId);

    @Select("SELECT f.* FROM face_record f \n" +
            "JOIN access_control a ON f.device_id = a.acce_id \n" +
            "JOIN project p ON a.proj_id = p.proj_id \n" +
            "JOIN organization o ON p.orga_id = o.orga_id \n" +
            "WHERE o.orga_id = #{orgaId} " +
            "ORDER BY f.create_time DESC LIMIT 5")
    List<FaceRecordEntity> getPersonintCount(@Param("orgaId") Integer orgaId);

    @Select("SELECT f.oper_name, f.duties, " +
            "SUM(CASE WHEN f.type = 0 THEN 1 ELSE 0 END) AS outCount, " +
            "SUM(CASE WHEN f.type = 1 THEN 1 ELSE 0 END) AS inCount " +
            "FROM face_record f " +
            "JOIN access_control a ON f.device_id = a.acce_id " +
            "JOIN project p ON a.proj_id = p.proj_id " +
            "JOIN organization o ON p.orga_id = o.orga_id " +
            "WHERE MONTH(f.create_time) = MONTH(NOW()) AND o.orga_id = #{orgaId} " +
            "GROUP BY f.oper_name, f.id")
    List<InOutCountVO> getInOutCount(@Param("orgaId") Integer orgaId);

    @Select("SELECT f.oper_name, f.duties, " +
            "HOUR(f.create_time) AS hour, " +
            "COUNT(*) as count " +
            "FROM face_record f " +
            "JOIN access_control a ON f.device_id = a.acce_id " +
            "JOIN project p ON a.proj_id = p.proj_id " +
            "JOIN organization o ON p.orga_id = o.orga_id " +
            "WHERE MONTH(f.create_time) = MONTH(NOW()) AND o.orga_id = #{orgaId} " +
            "GROUP BY f.oper_name, f.id, hour")
    List<HourlyStatsVO> getHourlyStats(@Param("orgaId") Integer orgaId);

    @Select("SELECT COUNT(*) FROM face_record f " +
            "JOIN access_control a ON f.device_id = a.acce_id " +
            "JOIN project p ON a.proj_id = p.proj_id " +
            "JOIN organization org ON p.orga_id = org.orga_id " +
            "WHERE f.create_time LIKE CONCAT(#{createTime,jdbcType=VARCHAR},'%') AND f.type = 1 AND org.orga_id = #{orgaId}")
    Integer getCreateTimeData(@Param("orgaId") Integer orgaId, @Param("createTime") String createTime);

    @Select("SELECT COUNT(*) FROM face_record f " +
            "JOIN access_control a ON f.device_id = a.acce_id " +
            "JOIN project p ON a.proj_id = p.proj_id " +
            "JOIN organization org ON p.orga_id = org.orga_id " +
            "WHERE f.create_time LIKE CONCAT(#{createTime,jdbcType=VARCHAR},'%') AND f.type = 0 AND org.orga_id = #{orgaId}")
    Integer getUpdateTimeData(@Param("orgaId") Integer orgaId, @Param("createTime") String createTime);

}